package com.abkj.platform.tail.controller;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import com.abkj.platform.tail.domain.Displacement;
import com.abkj.platform.tail.domain.Level;
import com.abkj.platform.tail.domain.Osmometer;
import com.abkj.platform.tail.domain.Rain;
import com.abkj.platform.tail.domain.Ultrasonic;
import com.abkj.platform.tail.service.SerialService;
import com.abkj.platform.tail.util.DateUtil;
import com.abkj.platform.tail.util.ExportExcelUtil;
import com.abkj.platform.tail.vo.DataVO;

@Controller
@RequestMapping("export")
public class ExportExcelController extends BaseController {
	
	private SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	@Autowired
	SerialService serialService;
	
	@RequestMapping("osmometer")
	public void osmometer(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "渗压计传感器列表.xls";
		List<Osmometer> osmometers = this.baseService.findAllObjects(Osmometer.class);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Osmometer> iterator = osmometers.iterator();
		while(iterator.hasNext()){
			Osmometer next = iterator.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("id", next.getId());
			map.put("code", next.getCode());
			map.put("name", next.getName());
			map.put("sectdes", next.getSectdes());
			map.put("depth", next.getDepth());
			map.put("state", 0==(Integer)next.getState() ? "启用" : "禁用");
			map.put("info", 0==(Integer)next.getInfo() ? "正常" : "异常");
			list.add(map);
		}
		
		String sheetName = "渗压计传感器";
		String[] fieldsKey = {"id","code","name","sectdes","depth","state","info"};
		String[] fieldsName = {"ID","编号","名称","安装断面","安装埋深(cm)","是否启用","通信状态"};
		Integer[] width = {5*256, 20*256, 20*256, 15*256, 15*256, 10*256, 10*256};
		String titleName = "渗压计传感器列表";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);

		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	private SimpleDateFormat newSimpleDateFormat(String string) {
		// TODO Auto-generated method stub
		return null;
	}
	@RequestMapping("displacement")
	public void displacement(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "位移计列表.xls";
		List<Displacement> displacements = this.baseService.findAllObjects(Displacement.class);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Displacement> iterator = displacements.iterator();
		while(iterator.hasNext()){
			Displacement next = iterator.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("id", next.getId());
			map.put("code", next.getCode());
			map.put("name", next.getName());
			map.put("num", next.getNum());
			map.put("init", next.getInit());
			map.put("alertup", next.getAlertup());
			map.put("alertdown", next.getAlertdown());
			map.put("type", 0==(Integer)next.getType() ? "测量点" : "基准点");
			map.put("state", 0==(Integer)next.getState() ? "启用" : "禁用");
			map.put("info", 0==(Integer)next.getInfo() ? "正常" : "异常");
			list.add(map);
		}
		
		String sheetName = "位移计列表";
		String[] fieldsKey = {"id","code","name","num","init","type","state","info","alertup","alertdown"};
		String[] fieldsName = {"ID","编号","名称","组号","初始值","类型","是否启用","通信状态","上升报警参数(mm)","下降报警参数(mm)"};
		Integer[] width = {5*256, 20*256, 20*256, 10*256, 10*256, 10*256, 10*256, 10*256, 10*256, 10*256};
		String titleName = "位移计列表";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("level")
	public void level(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "静力水准仪列表.xls";
		List<Level> levels = this.baseService.findAllObjects(Level.class);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Level> iterator = levels.iterator();
		while(iterator.hasNext()){
			Level next = iterator.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("id", next.getId());
			map.put("code", next.getCode());
			map.put("name", next.getName());
			map.put("num", next.getNum());
			map.put("init", next.getInit());
			map.put("alertup", next.getAlertup());
			map.put("alertdown", next.getAlertdown());
			map.put("type", 0==(Integer)next.getType() ? "测量点" : "基准点");
			map.put("state", 0==(Integer)next.getState() ? "启用" : "禁用");
			map.put("info", 0==(Integer)next.getInfo() ? "正常" : "异常");
			list.add(map);
		}
		
		String sheetName = "静力水准仪列表";
		String[] fieldsKey = {"id","code","name","num","init","type","state","info","alertup","alertdown"};
		String[] fieldsName = {"ID","编号","名称","组号","初始值","类型","是否启用","通信状态","上升报警参数(mm)","下降报警参数(mm)"};
		Integer[] width = {5*256, 20*256, 20*256, 10*256, 10*256, 10*256, 10*256, 10*256, 10*256, 10*256};
		String titleName = "静力水准仪列表";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("rain")
	public void rain(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "雨量计列表.xls";
		List<Rain> rains = this.baseService.findAllObjects(Rain.class);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Rain> iterator = rains.iterator();
		while(iterator.hasNext()){
			Rain next = iterator.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("code", next.getCode());
			map.put("name", next.getName());
			map.put("pxy", next.getPxy());
			map.put("alertval", next.getAlertval());
			map.put("state", 0==(Integer)next.getState() ? "启用" : "禁用");
			map.put("info", 0==(Integer)next.getInfo() ? "正常" : "异常");
			list.add(map);
		}
		
		String sheetName = "雨量计列表";
		String[] fieldsKey = {"code","name","pxy","state","info","alertval"};
		String[] fieldsName = {"编号","名称","安装位置","是否启用","通信状态","雨量上限报警参数(mm)"};
		Integer[] width = {20*256, 20*256, 10*256, 10*256, 10*256, 10*256};
		String titleName = "雨量计列表";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("ultrasonic")
	public void ultrasonic(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "超声波液位仪列表.xls";
		List<Ultrasonic> ultrasonic = this.baseService.findAllObjects(Ultrasonic.class);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Ultrasonic> iterator = ultrasonic.iterator();
		while(iterator.hasNext()){
			Ultrasonic next = iterator.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("code", next.getCode());
			map.put("name", next.getName());
			map.put("len", next.getLen());
			map.put("lenh", next.getLenh());
			map.put("pxy", next.getPxy());
			map.put("height", next.getHeight());
			map.put("level1", next.getLevel1());
			map.put("level2", next.getLevel2());
			map.put("state", 0==(Integer)next.getState() ? "启用" : "禁用");
			map.put("info", 0==(Integer)next.getInfo() ? "正常" : "异常");
			list.add(map);
		}
		
		String sheetName = "超声波液位仪列表";
		String[] fieldsKey = {"code","name","len","lenh","pxy","state","info","height","level1","level2"};
		String[] fieldsName = {"编号","名称","最小干滩长度","最小干滩长度处水位线","安装位置","是否启用","通信状态","安装处标高(cm)","一级报警参数(cm)","二级报警参数(cm)"};
		Integer[] width = {20*256, 15*256, 15*256, 10*256, 10*256, 10*256, 10*256, 10*256, 10*256, 10*256};
		String titleName = "超声波液位仪列表";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("osmometer_ana")
	public void osmometer_ana(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "浸润线分析.xls";
		
		List<Map> sensors = this.baseService.findBySql("SELECT * FROM osmometer ORDER BY case when sectdes='断面一' then 1 when sectdes='断面二' then 2 when sectdes='断面三' then 3 when sectdes='断面四' then 4 when sectdes='断面五' then 5 when sectdes='断面六' then 6 when sectdes='断面七' then 7 when sectdes='断面八' then 8 when sectdes='断面九' then 9 when sectdes='断面十' then 10 else 99 end asc");
		
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Map> iterator = sensors.iterator();
		//横表变竖表
		//Map<String, Object> row1 = new HashMap<String, Object>();
		Map<String, Object> classMap = new LinkedHashMap<String, Object>();//记录每种断面数目
							classMap.put("断面", 1);
		Map<String, Object> sectdesMap = new LinkedHashMap<String, Object>();//记录每种断面类型
							sectdesMap.put("key0", "断面");
		Map<String, Object> nameMap = new LinkedHashMap<String, Object>();//记录渗压计
							nameMap.put("key0", "渗压计");
		Map<String, Object> depthMap = new LinkedHashMap<String, Object>();//记录埋设深度
							depthMap.put("key0", "埋设深度CM");
		Map<String, Object> currentMap = new LinkedHashMap<String, Object>();//记录浸润线深度:depth - cm
							currentMap.put("key0", "浸润线深度CM");
		Map<String, Object> cmMap = new LinkedHashMap<String, Object>();//记录水位变化量 cm=Double.valueOf(offer / 9.800000000000001D * 100.0D
							cmMap.put("key0", "水位变化量CM");
		Map<String, Object> alarmMap = new LinkedHashMap<String, Object>();//记录报警深度
							alarmMap.put("key0", "报警深度CM");
		Map<String, Object> infoMap = new LinkedHashMap<String, Object>();//记录当前状态
							infoMap.put("key0", "当前状态");
		
		int index = 1;
		while(iterator.hasNext()){
			
			Map next = iterator.next();
			Object sectdesObj = next.get("sectdes");
			if (sectdesObj != null) {
				Object valueObj = classMap.get(sectdesObj);
				if (valueObj == null) {
					classMap.put(sectdesObj.toString(), 1);
				}else {
					classMap.put(sectdesObj.toString(), Integer.parseInt(valueObj.toString())+1);
				}
				sectdesMap.put("key"+index, sectdesObj);
			}else {
				sectdesMap.put("key"+index, "未知");
			}
			
			Object nameObj = next.get("name");
			if (nameObj == null) {
				nameMap.put("key"+index, "未知");
			}else {
				nameMap.put("key"+index, nameObj);
			}
			
			Object depthObj = next.get("depth");
			float depth = 0;
			if (depthObj != null) {
				depth = Float.parseFloat(depthObj.toString());
			}
			depthMap.put("key"+index, depth);
			
			//float offer = NumberUtils.toFloat(String.valueOf(m.get("offer")));
			//m.put("cm", Double.valueOf(offer / 9.800000000000001D * 100.0D));
			Object offerObj = next.get("offer");
			float offer = 0;
			if (offerObj != null) {
				offer = Float.parseFloat(offerObj.toString());
			}
			double cm = Double.valueOf(offer / 9.800000000000001D * 100.0D);
			cmMap.put("key"+index, cm);
			
			currentMap.put("key"+index, depth - cm);
			
			Object alarmObj = next.get("alarm");
			float alarm = 0;
			if (alarmObj != null) {
				alarm = Float.parseFloat(alarmObj.toString());
			}
			alarmMap.put("key"+index, alarm);
			
			Object infoObj = next.get("info");
			if (infoObj == null) {
				infoMap.put("key"+index, "未知");
			}else {
				switch ((int)infoObj) {
				case 0:
					infoMap.put("key"+index, "正常");
					break;
				case 1:
					infoMap.put("key"+index, "校验错误");
					break;
				case -1:
					infoMap.put("key"+index, "断线");
					break;

				default:
					infoMap.put("key"+index, "未知");
					break;
				}
			}
			index++;
		}
		list.add(sectdesMap);
		list.add(nameMap);
		list.add(depthMap);
		list.add(currentMap);
		list.add(cmMap);
		list.add(alarmMap);
		list.add(infoMap);
		
		String sheetName = "浸润线分析";
		String[] fieldsKey = new String[index];
		String[] fieldsName = new String[index];//可全部为空
		for(int i=0; i<index; i++){
			fieldsKey[i] = "key"+i;
		}
		Integer[] width = {15*256, 15*256, 15*256, 15*256, 15*256, 15*256, 15*256, 15*256, 15*256};
		String titleName = "浸润线分析";
		
		int location1 = 2;
		int location2 = 2;
		int location3 = 0;
		int location4 = 0;
		Set<String> keySet = classMap.keySet();
		List<Integer[]> region = new ArrayList<Integer[]>();
		Integer[] row1 = {1,1,0,list.size()+1};
		region.add(row1);
		for (String key : keySet) {
			Integer[] ints = new Integer[4];
			Object countObj = classMap.get(key);
			if (countObj != null) {
				int count = Integer.parseInt(countObj.toString());
				location4 += count - 1;
				ints[0] = location1;
				ints[1] = location2;
				ints[2] = location3;
				ints[3] = location4;
				location3 += count;
				location4 = location3;
				region.add(ints);
			}
		}
		
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list, region);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("displacement_ana")
	public void displacement_ana(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "边坡移动距离.xls";
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		List<Map> basicDatas = this.baseService.findBySql("select * from displacement where type!=1 order by num");
		List<Map> baseDatas = this.baseService.findBySql("select * from displacement where type=1");
		float baseShould1 = 0.0F;
		float baseShould2 = 0.0F;
		float baseInit1 = 0.0F;
		float baseInit2 = 0.0F;
		for (Map m : baseDatas) {
			if (m.get("num").toString().equals("1")) {
				baseShould1 = NumberUtils.toFloat(m.get("should").toString());
				baseInit1 = NumberUtils.toFloat(m.get("init").toString());
			} else {
				baseShould2 = NumberUtils.toFloat(m.get("should").toString());
				baseInit2 = NumberUtils.toFloat(m.get("init").toString());
			}
		}
		Iterator<Map> iterator = basicDatas.iterator();
		while(iterator.hasNext()){
			Map<String, Object> map = new HashMap<String, Object>();
			Map next = iterator.next();
			map.put("name", next.get("name"));
			Object shouldObj = next.get("should");
			float should = 0;
			if (shouldObj != null) {
				should = Float.parseFloat(shouldObj.toString());
			}
			Object initObj = next.get("init");
			float init = 0;
			if (initObj != null) {
				init = Float.parseFloat(initObj.toString());
			}
			map.put("should", should);
			float stateValue1 = (baseShould1-should) - (baseInit1-init);
			float stateValue2 = (baseShould2-should) - (baseInit2-init);
			Object numObj = next.get("num");
			if (numObj != null) {
				switch (numObj.toString()) {
				case "1":
					map.put("opChange", baseShould1-should);
					map.put("baseShould", baseShould1);
					map.put("state", stateValue1==0 ? "不变" : stateValue1);
					break;
				case "2":
					map.put("opChange", baseShould2-should);
					map.put("baseShould", baseShould2);
					map.put("state", stateValue2==0 ? "不变" : stateValue2);
					break;
				default:
					break;
				}
			}
			list.add(map);
		}
		
		String sheetName = "边坡移动距离";
		String[] fieldsKey = {"name","should","opChange","baseShould","state"};
		String[] fieldsName = {"测点名称","当前边坡位置(mm)","相对零点位置的移动量量","测量点初始值(mm)","边坡移动状态"};
		Integer[] width = {20*256, 20*256, 20*256, 20*256, 10*256};
		String titleName = "边坡移动距离";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("level_ana")
	public void level_ana(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "表面垂直位移.xls";
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		List<Map> basicDatas = this.baseService.findBySql("select * from level where type!=1 order by num");
		List<Map> baseDatas = this.baseService.findBySql("select * from level where type=1");
		float baseShould1 = 0.0F;
		float baseShould2 = 0.0F;
		float baseInit1 = 0.0F;
		float baseInit2 = 0.0F;
		for (Map m : baseDatas) {
			if (m.get("num").toString().equals("1")) {
				baseShould1 = NumberUtils.toFloat(m.get("should").toString());
				baseInit1 = NumberUtils.toFloat(m.get("init").toString());
			} else {
				baseShould2 = NumberUtils.toFloat(m.get("should").toString());
				baseInit2 = NumberUtils.toFloat(m.get("init").toString());
			}
		}
		Iterator<Map> iterator = basicDatas.iterator();
		while(iterator.hasNext()){
			Map<String, Object> map = new HashMap<String, Object>();
			Map next = iterator.next();
			map.put("name", next.get("name"));
			map.put("alertup", next.get("alertup"));
			map.put("alertdown", next.get("alertdown"));
			Object shouldObj = next.get("should");
			float should = 0;
			if (shouldObj != null) {
				should = Float.parseFloat(shouldObj.toString());
			}
			Object initObj = next.get("init");
			float init = 0;
			if (initObj != null) {
				init = Float.parseFloat(initObj.toString());
			}
			map.put("should", should);
			float stateValue1 = (baseShould1-should) - (baseInit1-init);
			float stateValue2 = (baseShould2-should) - (baseInit2-init);
			Object numObj = next.get("num");
			if (numObj != null) {
				switch (numObj.toString()) {
				case "1":
					map.put("opChange", baseShould1-should);
					map.put("baseShould", baseShould1);
					map.put("state", stateValue1==0 ? "不变" : stateValue1);
					break;
				case "2":
					map.put("opChange", baseShould2-should);
					map.put("baseShould", baseShould2);
					map.put("state", stateValue2==0 ? "不变" : stateValue2);
					break;
				default:
					break;
				}
			}
			list.add(map);
		}
		
		String sheetName = "表面垂直位移";
		String[] fieldsKey = {"name","should","opChange","baseShould","state"};
		String[] fieldsName = {"测点名称","当前液位高度值(mm)","相对基准点的沉降变化量","当前基准点液位高度值(mm)","沉降状态","上升报警参数(mm)","下降报警参数(mm)"};
		Integer[] width = {20*256, 20*256, 20*256, 20*256, 10*256};
		String titleName = "表面垂直位移";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("ultrasonic_ana")
	public void ultrasonic_ana(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String realFileName = "库水位与干滩.xls";
		List<Ultrasonic> ultrasonic = this.baseService.findAllObjects(Ultrasonic.class);
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<Ultrasonic> iterator = ultrasonic.iterator();
		while(iterator.hasNext()){
			Ultrasonic next = iterator.next();
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("len", next.getLen());
			float lenh = next.getLenh();
			map.put("lenh", lenh);
			float height = next.getHeight();
			float should = next.getShould();
			float nowHeight = height - should;
			map.put("height", height);
			map.put("nowHeight", nowHeight);
			map.put("level1", next.getLevel1());
			map.put("level2", next.getLevel2());
			map.put("state1", nowHeight <= lenh ? "正常" : "水位超限");
			map.put("state2", nowHeight <= lenh ? "正常" : "水位超限");
			list.add(map);
		}
		String sheetName = "库水位与干滩";
		String[] fieldsKey = {"len","lenh","height","nowHeight","state1","state2","alertup","alertdown","level1","level2"};
		String[] fieldsName = {"最小干滩长度cm","最小干滩长度处水位线cm","安装处标高cm","实时水位cm","水位状态","干滩状态","一级报警参数(cm)","二级报警参数(cm)"};
		Integer[] width = {20*256, 20*256, 15*256, 15*256, 15*256, 15*256, 10*256, 10*256, 10*256, 10*256};
		String titleName = "库水位与干滩";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("osmometer_his/{senid}/{begin}/{end}")
	public void osmometer_his(HttpServletRequest request,HttpServletResponse response, 
			@PathVariable int senid, @PathVariable String begin, 
			@PathVariable String end) throws IOException {
		
		String beginTime = begin + " 00:00:00";
		String endTime = end + " 23:59:59";
		System.out.println("senid=" + senid + "&beginTime=" + beginTime + "&endTime=" + endTime);
		String sql = "select * from osmometerdata where sid=" + senid + " and createtime>='" + beginTime
				+ "' and createtime<='" + endTime + "' order by createtime";
		List datas = this.baseService.findBySql(sql);
		List osmometerMap = this.baseService.findBySql("select * from osmometer where id=" + senid);
		float baseShould = 0.0F;
		if ((osmometerMap != null) && (osmometerMap.size() > 0)) {
			Map osmometer = (Map) osmometerMap.get(0);
			baseShould = NumberUtils.toFloat(String.valueOf(osmometer.get("should")))
					- NumberUtils.toFloat(String.valueOf(osmometer.get("offer")));
		}
		Map ret = createData(1, datas, baseShould);
		List<DataVO> voList = (List<DataVO>)ret.get("data");
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<DataVO> iterator = voList.iterator();
		while (iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			DataVO dataVO = iterator.next();
			long time = dataVO.getX();
			Date date = new Date(time);
			map.put("time", df.format(date));
			map.put("value", dataVO.getY());
			list.add(map);
		}
		
		String realFileName = "渗压计传感器历史数据.xls";
		
		String sheetName = "渗压计传感器历史数据";
		String[] fieldsKey = {"time","value"};
		String[] fieldsName = {"时间","变化值"};
		Integer[] width = {20*256, 20*256};
		String titleName = "渗压计传感器历史数据";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);

		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("displacement_his/{senid}/{begin}/{end}")
	public void displacement_his(HttpServletRequest request,HttpServletResponse response, 
			@PathVariable int senid, @PathVariable String begin, 
			@PathVariable String end) throws IOException {
		
		String beginTime = begin + " 00:00:00";
		String endTime = end + " 23:59:59";
		System.out.println("senid=" + senid + "&beginTime=" + beginTime + "&endTime=" + endTime);
		String sql = "select * from displacementdata where sid=" + senid + " and createtime>='" + beginTime
				+ "' and createtime<='" + endTime + "' order by createtime";
		List datas = this.baseService.findBySql(sql);
		Map ret = createData(2, datas, 0.0F);
		
		List<DataVO> voList = (List<DataVO>)ret.get("data");
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<DataVO> iterator = voList.iterator();
		while (iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			DataVO dataVO = iterator.next();
			long time = dataVO.getX();
			Date date = new Date(time);
			map.put("time", df.format(date));
			map.put("value", dataVO.getY());
			list.add(map);
		}
		
		String realFileName = "位移计历史数据.xls";
		
		String sheetName = "位移计历史数据";
		String[] fieldsKey = {"time","value"};
		String[] fieldsName = {"时间","变化值"};
		Integer[] width = {20*256, 20*256};
		String titleName = "位移计历史数据";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("level_his/{senid}/{begin}/{end}")
	public void level_his(HttpServletRequest request,HttpServletResponse response, 
			@PathVariable int senid, @PathVariable String begin, 
			@PathVariable String end) throws IOException {
		String beginTime = begin + " 00:00:00";
		String endTime = end + " 23:59:59";
		System.out.println("senid=" + senid + "&beginTime=" + beginTime + "&endTime=" + endTime);
		String sql = "select * from leveldata where sid=" + senid + " and createtime>='" + beginTime
				+ "' and createtime<='" + endTime + "' order by createtime";
		List datas = this.baseService.findBySql(sql);
		Map ret = createData(2, datas, 0.0F);
		
		List<DataVO> voList = (List<DataVO>)ret.get("data");
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<DataVO> iterator = voList.iterator();
		while (iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			DataVO dataVO = iterator.next();
			long time = dataVO.getX();
			Date date = new Date(time);
			map.put("time", df.format(date));
			map.put("value", dataVO.getY());
			list.add(map);
		}
		
		String realFileName = "静力水准仪历史数据.xls";
		
		String sheetName = "静力水准仪历史数据";
		String[] fieldsKey = {"time","value"};
		String[] fieldsName = {"时间","变化值"};
		Integer[] width = {20*256, 20*256};
		String titleName = "静力水准仪历史数据";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("rain_his/{condition}/{time}")
	public void rain_his(HttpServletRequest request,HttpServletResponse response, 
			@PathVariable int condition, @PathVariable String time) throws IOException {
		time = time.replaceAll("-", "/");
		System.out.println("condition=" + condition + "&time=" + time);
		Map ret = parseRainData(time, condition);
		
		String realFileName = "雨量计历史数据(按天).xls";
		String sheetName = "雨量计历史数据(按天)";
		String titleName = "雨量计历史数据(按天)";
		
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		List<Float> voList = (List<Float>)ret.get("xs");
		if (condition == 1) {//查询1-天或2-月数据
			for (int i = 0,length=voList.size(); i < length; i++){
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("time", (i+1)+":00");
				map.put("value", voList.get(i));
				list.add(map);
			}
		}else if (condition == 2) {
			realFileName = "雨量计历史数据(按月).xls";
			sheetName = "雨量计历史数据(按月)";
			titleName = "雨量计历史数据(按月)";
			for (int i = 0,length=voList.size(); i < length; i++){
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("time", (i+1)+"号");
				map.put("value", voList.get(i));
				list.add(map);
			}
		}else {
			
		}
		
		String[] fieldsKey = {"time","value"};
		String[] fieldsName = {"时间","变化值"};
		Integer[] width = {20*256, 20*256};
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	@RequestMapping("ultrasonic_his/{begin}/{end}")
	public void ultrasonic_his(HttpServletRequest request,HttpServletResponse response, 
			@PathVariable String begin, 
			@PathVariable String end) throws IOException {
		String basicQuery = "SELECT * FROM ultrasonic limit 1";
		List basic = this.baseService.findBySql(basicQuery);
		float height = 0.0F;
		float min = 0.0F;
		float max = 0.0F;
		if ((basic != null) && (basic.size() != 0)) {
			Map basicU = (Map) basic.get(0);
			height = NumberUtils.toFloat(String.valueOf(basicU.get("height")));
		}

		String beginTime = begin + " 00:00:00";
		String endTime = end + " 23:59:59";
		List<Map> ultrasonics = this.baseService.findBySql("select * from ultrasonicdata where createtime>='" + beginTime
				+ "' and createtime<='" + endTime + "' order by createtime");
		Map ret = new HashMap();
		List datas = new ArrayList();
		if ((ultrasonics != null) && (ultrasonics.size() > 0)) {
			Map tmp = (Map) ultrasonics.get(0);
			max = min = height - NumberUtils.toFloat(String.valueOf(tmp.get("should")));
		}
		for (Map map : ultrasonics) {
			long x = DateUtil.strToLong(String.valueOf(map.get("createtime")), "yyyy-MM-dd HH:mm:ss");
			float y = NumberUtils.toFloat(String.valueOf(map.get("should")));
			float val = height - y;
			if (max < val) {
				max = val;
			}
			if (min > val) {
				min = val;
			}
			DataVO dv = new DataVO();
			dv.setX(x);
			dv.setY(val);
			datas.add(dv);
		}
		ret.put("data", datas);
		ret.put("min", Float.valueOf(min));
		ret.put("max", Float.valueOf(max));
		
		List<DataVO> voList = (List<DataVO>)ret.get("data");
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Iterator<DataVO> iterator = voList.iterator();
		while (iterator.hasNext()) {
			Map<String, Object> map = new HashMap<String, Object>();
			DataVO dataVO = iterator.next();
			map.put("time", dataVO.getX());
			map.put("value", dataVO.getY());
			list.add(map);
		}
		
		String realFileName = "超声波液位仪历史数据.xls";
		
		String sheetName = "超声波液位仪历史数据";
		String[] fieldsKey = {"time","value"};
		String[] fieldsName = {"时间","变化值"};
		Integer[] width = {20*256, 20*256};
		String titleName = "超声波液位仪历史数据";
		HSSFWorkbook workbook = ExportExcelUtil.list2Bytes(titleName, sheetName, fieldsKey, fieldsName, width, list);
		
		OutputStream outputStream = null;
		try {
			realFileName = URLEncoder.encode(realFileName, "UTF-8");
			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ realFileName + "\"");
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			workbook.write(outputStream);
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	private Map<String, Object> createData(int sentype, List<Map> datas, float baseShould) {
		Map ret = new HashMap();
		List ds = new ArrayList();
		float min = 0.0F;
		float max = 0.0F;

		if (sentype == 2) {
			if ((datas != null) && (datas.size() > 0)) {
				Map tmp = (Map) datas.get(0);
				max = min = NumberUtils.toFloat(String.valueOf(tmp.get("change")));
			}

			for (Map m : datas) {
				long x = DateUtil.strToLong(String.valueOf(m.get("createtime")), "yyyy-MM-dd HH:mm:ss");
				float y = NumberUtils.toFloat(String.valueOf(m.get("change")));

				float val = y;
				if (max < val) {
					max = val;
				}
				if (min > val) {
					min = val;
				}
				DataVO dv = new DataVO();
				dv.setX(x);
				dv.setY(val);
				ds.add(dv);
			}
		} else {
			float should;
			if ((datas != null) && (datas.size() > 0)) {
				Map tmp = (Map) datas.get(0);
				should = NumberUtils.toFloat(String.valueOf(tmp.get("should")));
				float offer = NumberUtils.toFloat(String.valueOf(tmp.get("offer")));
				if (baseShould != 0.0F) {
					offer = should - baseShould;
				}
				float y = (float) (offer / 9.800000000000001D * 100.0D);
				max = min = y;
			}

			for (Map m : datas) {
				long x = DateUtil.strToLong(String.valueOf(m.get("createtime")), "yyyy-MM-dd HH:mm:ss");
				float should1 = NumberUtils.toFloat(String.valueOf(m.get("should")));
				float offer = NumberUtils.toFloat(String.valueOf(m.get("offer")));
				if (baseShould != 0.0F) {
					offer = should1 - baseShould;
				}
				float y = (float) (offer / 9.800000000000001D * 100.0D);
				if (max < y) {
					max = y;
				}
				if (min > y) {
					min = y;
				}
				DataVO dv = new DataVO();
				dv.setX(x);
				dv.setY(y);
				ds.add(dv);
			}
		}
		ret.put("min", Float.valueOf(min));
		ret.put("max", Float.valueOf(max));
		ret.put("data", ds);
		return ret;
	}
	private Map<String, Object> parseRainData(String time, int condition) {
		int date = NumberUtils.toInt(time.replaceAll("/", ""));
		Map ret = new HashMap();
		List<Float> xs = new ArrayList();
		int daysOfMonth = daysOfMonth(time);
		float max = 0.0F;
		float min = 0.0F;
		float allday = 0.0F;
		int date02;
		if (condition == 1) {
			List rains = this.baseService.findBySql("select * from raindata where day=" + date);
			if (rains.size() > 0) {
				Map map = (Map) rains.get(0);
				String allVal = String.valueOf(map.get("allval"));
				allday = NumberUtils.toFloat(String.valueOf(map.get("val")));
				int len = allVal.length() / 4;
				for (int i = 0; i < 24; i++)
					if (i < len) {
						String tmp = allVal.substring(i * 4, i * 4 + 4);
						xs.add(Float.valueOf(Integer.parseInt(tmp, 16) * 0.1F));
					} else {
						xs.add(null);
					}
			} else {
				for (int i = 0; i < 24; i++)
					xs.add(null);
			}
		} else {
			int date01 = date * 100;
			date02 = date * 100 + 31;
			List<Map> rains = this.baseService.findBySql(
					"select * from raindata where day>=" + date01 + " and day<=" + date02 + " order by createtime");
			int len = rains.size();
			boolean hasData = false;
			for (int i = 1; i <= daysOfMonth; i++) {
				for (Map map : rains) {
					String days = String.valueOf(map.get("day"));
					int day = NumberUtils.toInt(days.substring(days.length() - 2, days.length()));
					if (day == i) {
						int v = NumberUtils.toInt(String.valueOf(map.get("val")));
						xs.add(Float.valueOf(v * 0.1F));
						hasData = true;
						break;
					}
					if (day > i) {
						hasData = false;
						break;
					}
					hasData = false;
				}

				if (!hasData) {
					xs.add(null);
				}
			}
		}

		for (Float x : xs) {
			if (x != null) {
				if (x.floatValue() > max) {
					max = x.floatValue();
				}
				if (x.floatValue() < min)
					min = x.floatValue();
			}
		}
		ret.put("val", Double.valueOf(allday * 0.1D));
		ret.put("max", Float.valueOf(max));
		ret.put("min", Float.valueOf(min));
		ret.put("xs", xs);
		ret.put("days", Integer.valueOf(daysOfMonth));
		return ret;
	}
	public int daysOfMonth(String date) {
		String[] dates = date.split("/");
		Calendar c = Calendar.getInstance();
		c.set(1, NumberUtils.toInt(dates[0]));
		c.set(2, NumberUtils.toInt(dates[1]) - 1);
		int days = c.getActualMaximum(5);
		return days;
	}


}
